package top.majuan.study.study.dao;

import top.majuan.study.study.entity.Tbsoftware;
import top.majuan.study.study.entity.Tbsoftwaretype;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbsoftwareDAO {
    public static final String INSERT = "insert into tb_software(type_id,software_name,description,vendor) values(?,?,?,?)";
    public static final String QUERY = "select a.*,b.type_name,b.description from tb_software a inner join tb_software_type b on a.type_id = b.id";
    public static final String QUERY_BY_ID = "select * from tb_software where id = ?";
    public static final String DELETE_BY_TYPE_ID = "delete from tb_software where type_id = ?";
    public static final String UPDATE = "update tb_software set type_id = ?,software_name = ?,description = ?,vendor = ? where id = ?";

    public static int update(Tbsoftware software) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, software.getType_id());
        ps.setString(2, software.getSoftware_name());
        ps.setString(3, software.getDescription());
        ps.setString(4, software.getVendor());
        ps.setInt(5, software.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;

    }

    public static int deleteById(Integer typeid) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE_BY_TYPE_ID);
        ps.setInt(1, typeid);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static Tbsoftware queryById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        Tbsoftware software = null;
        if (rs.next()) {
            software = new Tbsoftware();
            software.setType_id(rs.getString("type_id"));
            software.setSoftware_name(rs.getString("software_name"));
            software.setDescription(rs.getString("description"));
            software.setVendor(rs.getString("vendor"));
        }
        conn.close();
        return software;
    }

    public static List<Tbsoftware> query() throws Exception {
        Connection conn = DBHelper.getConnection();
        List<Tbsoftware> list = new ArrayList<>();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Tbsoftware software = new Tbsoftware();
            software.setType_id(rs.getString("type_id"));
            software.setSoftware_name(rs.getString("software_name"));
            software.setDescription(rs.getString("description"));
            software.setVendor(rs.getString("vendor"));

            Tbsoftwaretype type = new Tbsoftwaretype();
            type.setType_name(rs.getString("type_name"));
            type.setDescription(rs.getString("description"));
            software.setType(type);

            list.add(software);
        }
        conn.close();
        return list;
    }

    public static int insert(Tbsoftware software) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, software.getType_id());
        ps.setString(2, software.getSoftware_name());
        ps.setString(3, software.getDescription());
        ps.setString(4, software.getVendor());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
}
